Imports System.Data.OleDb
Imports RIBS.Code.DBCode
Imports RIBS.Code.GlobalModule1

Public Class RibsClass
    Public Enum ReturnType
        Main = 0
        Tasks = 1
    End Enum

    Function GetTemplate() As DataSet
        Dim ds As New DataSet
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String = "SELECT Top 1 * FROM Ribs "
            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)            
            Adapter.Fill(ds, "Ribs")

            ds.Tables.Add(GetActionHistory(ds.Tables("Ribs").Rows(0).Item("RIBSID")))

            ds.Relations.Add("HistoryItems", ds.Tables("Ribs").Columns("RibsID"), ds.Tables("ActionHistory").Columns("RibsID"))

        Catch ex As Exception
            Throw ex
        End Try
        Return ds
    End Function

    Public Sub LogAllocation(ByVal ribsID As Int32, ByVal UserID As String, ByVal Allocated As Boolean)
        Try
            If Allocated Then
                InsertNewAction(ribsID, "Allocated", "Allocated To: " & UserID, Now, "Other", "Allocated", "", NetWorkUserID)
            Else
                InsertNewAction(ribsID, "Unallocated", "Unallocated", Now, RIBSState.Unallocated.ToString, "Unallocated", "", NetWorkUserID)
            End If        
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function AllocateWork(ByVal AllocateData As String) As Boolean
        Try
            Dim WorkArray() As String = Split(AllocateData, "|")
            Dim RibsIDs As String = ""
            Dim unAllocated As Boolean = False
            For Each st As String In WorkArray
                If st <> "" Then
                    RibsIDs += Left$(st, InStr(st, ",") - 1) & ","
                    If unAllocated <> True Then
                        If "Unallocated" = Mid(st, (InStr(st, ",") + 1), Len(st) - 1) Then
                            unAllocated = True
                        End If
                    End If
                End If
            Next

            Dim dt As DataTable = GetRibsItems(RibsIDs)

            For Each dr As DataRow In dt.Rows
                Dim UserID As String = ""
                For Each st As String In WorkArray
                    If st <> "" Then
                        If Left$(st, InStr(st, ",") - 1) = dr("RibsID") Then
                            UserID = Mid$(st, InStr(st, ",") + 1)
                            'If UserID = "None" Then UserID = ""
                            Exit For
                        End If
                    End If
                Next

                If unAllocated Then
                    LogAllocation(dr("RIBSID"), RIBSState.Unallocated.ToString, False)
                    ExecuteNonQuery("UPDATE Ribs SET USERID = '" & RIBSState.Unallocated.ToString & "', " & _
                                    " unAllocDate = " & dbDateFormat(DateAndTime.Now) & " " & _
                                    " WHERE RibsID =" & dr("RIBSID"))
                Else
                    LogAllocation(dr("RIBSID"), UserID, True)
                    ExecuteNonQuery("UPDATE Ribs SET USERID = '" & UserID & "', " & _
                                    " AllocDate = " & dbDateFormat(DateAndTime.Now) & " " & _
                                    " WHERE RibsID =" & dr("RIBSID"))
                End If

            Next
            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function Save(ByVal ds As DataSet, ByVal ID As Int32) As Boolean
        Try
            If ds.Tables("Ribs").Rows.Count > 0 Then
                Dim SQL As String = "Select * from Ribs"
                Dim Conn As New OleDbConnection(gConnectionString)
                Dim Adapter As New OleDbDataAdapter
                Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
                Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(Adapter)
                Conn.Open()
                Adapter.Update(ds, "Ribs")
                Conn.Close()
            End If
            Return True
        Catch ex As Exception
            Throw ex
        End Try
        'Try
        '    Dim Conn As New OleDbConnection(gConnectionString)
        '    Dim dt As DataTable = ds.Tables("Ribs")
        '    Dim cmd As New OleDbCommand
        '    Dim daCol As DataColumn
        '    Dim SQL As String = "Update Ribs Set "

        '    For Each daCol In dt.Columns                
        '        If UCase(daCol.ColumnName) <> "RibsID" Then
        '            SQL += " " & daCol.ColumnName & " = "
        '            Debug.Print(daCol.ColumnName & ": " & daCol.DataType.ToString)
        '            Select Case daCol.DataType.ToString
        '                Case "System.String"
        '                    Dim s As String
        '                    s = IIf(IsDBNull(dt.Rows(0)(daCol.ColumnName)), "", dt.Rows(0)(daCol.ColumnName))
        '                    s = Replace(s, "'", "")
        '                    s = Replace(s, """", "")
        '                    SQL += "'" & s & "'"
        '                Case "System.Int32"
        '                    Dim s As Int32
        '                    s = IIf(IsDBNull(dt.Rows(0)(daCol.ColumnName)), 0, dt.Rows(0)(daCol.ColumnName))
        '                    SQL += IIf(IsNumeric(s), CStr(s), 0)
        '                Case "System.Decimal"
        '                    Dim s As Double
        '                    s = IIf(IsDBNull(dt.Rows(0)(daCol.ColumnName)), 0, dt.Rows(0)(daCol.ColumnName))
        '                    SQL += IIf(IsNumeric(s), CStr(s), 0)
        '                Case "System.DateTime"
        '                    SQL += "#" & IIf(IsDate(dt.Rows(0)(daCol.ColumnName)), CStr(Format(dt.Rows(0)(daCol.ColumnName), "dd MMM yyyy")), 0) & "#"
        '                Case "System.Boolean"
        '                    SQL += IIf(dt.Rows(0)(daCol.ColumnName), "1", "0")
        '            End Select
        '            SQL += " ,"
        '        End If
        '    Next
        '    SQL = Left(SQL, Len(SQL) - 1)
        '    SQL += " Where ID = " & CStr(ID)
        '    Conn.Open()
        '    cmd.Connection = Conn
        '    cmd.CommandText = SQL
        '    cmd.ExecuteNonQuery()
        '    Conn.Close()
        '    Return True
        'Catch ex As Exception
        '    Throw ex
        'End Try

    End Function

    Function AddNew() As DataSet
        Dim RECORD_ID As String
        Dim ds As New DataSet
        Dim con As New OleDbConnection(gConnectionString)
        Dim SQL As String = "SELECT count([RECORD_ID]) as RwCount From Ribs Where [RECORD_ID] LIKE 'Manual%'"
        Dim cmd As New OleDbCommand(SQL, con)
        Dim adapter As New OleDbDataAdapter(cmd)
        Try
            adapter.Fill(ds, "Ribs")
            If ds.Tables("Ribs").Rows.Count > 0 Then
                RECORD_ID = "Manual" & CStr(ds.Tables("Ribs").Rows(0).Item("RwCount") + 1)
            Else
                RECORD_ID = "Manual" & "1"
            End If

            ds.Clear()
            con.Open()
            cmd.Connection = con

            cmd.CommandText = "INSERT INTO Ribs ([RECORD_ID],POLICY_NUMBER,  ManuallyAdded ,UserID, Retained, WriteOff, AMOUNT_DUE, CURRENT_PREMIUM_AMOUNT, NEW_PREMIUM_AMOUNT) " & _
                                  "VALUES ('" & RECORD_ID & "','" & RECORD_ID & "', True, '" & CurrentUser & "', 0.00, 0.00, 0.00, 0.00, 0.00)"
            cmd.ExecuteNonQuery()

            con.Close()
            cmd.CommandText = "Select * from Ribs Where [RECORD_ID] = '" & RECORD_ID & "'"
            adapter.SelectCommand = cmd
            adapter.Fill(ds, "Ribs")
            If ds.Tables("Ribs").Rows.Count > 0 Then
                Return GetRibsItem(CInt(ds.Tables("Ribs").Rows(0).Item("RibsID")))
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

   
    Function GetRibsList(Optional ByVal Search As String = "", _
                         Optional ByVal rtValue As ReturnType = ReturnType.Main, _
                         Optional ByVal Archive As String = "") As DataSet
        Dim ds As New DataSet
        Try
            gConnectionString = GetdbConnection()
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            If Archive.ToUpper.Contains("ARCHIVE") Then
                Archive = "_Archive"
            Else
                Archive = String.Empty
            End If

            'Get Archived data here 
            Dim SQL As String = "SELECT distinct 0 as IsRowVisible, " & _
                                " (SelecT TOP 1 AH.EntryDate     From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as EntryDate, " & _
                                " (SelecT TOP 1 AH.ActionDate    From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as ActionDate, " & _
                                " (SelecT TOP 1 AH.ActionID      From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as ActionID, " & _
                                " (SelecT TOP 1 AH.ContactMethod From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as ContactMethod,  " & _
                                " (SelecT TOP 1 AH.[Action]      From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as [Action], " & _
                                " (SelecT TOP 1 AH.[User]        From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as [User], " & _
                                " (SelecT TOP 1 AH.RibsState     From  ActionHistory" & Archive & " AH WHERE AH.RibsID = RD.RibsID ORDER BY AH.ActionID DESC) as RibsState, " & _
                                " RD.*, LETTER_CODE.Description " & _
                                " FROM (LETTER_CODE RIGHT JOIN Ribs" & Archive & " RD ON LETTER_CODE.LETTER_CODE = RD.LETTER_CODE) " & _
                                " LEFT JOIN ActionHistory" & Archive & " AH2 ON RD.RibsID = AH2.RibsID " & _
                                ""
            '" (SelecT top 1 ActionHistory.[Note] from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as [Note], " & _
            'Dim SQL As String = "SELECT DISTINCT ActionHistory.*, Ribs.*,  LETTER_CODE.Description " & _
            '                                    " FROM (LETTER_CODE RIGHT JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) " & _
            '                                    " LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID "
            If Search = "" Then
                SQL += " WHERE " & _
                        " (" & GetStatusFilter(rtValue) & ") " 'ORDER BY ActionHistory.EntryDate DESC"
            Else
                'SQL += " WHERE ((Select Top 1 ActionID  from  ActionHistory Where RibsID = Ribs.RibsID order By ActionHistory.EntryDate Desc) =[ActionHistory].[actionid] ) "
                'SQL = " Select * from Ribs  "
                SQL += GetSearchSQL(Search)
            End If

            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            Adapter.Fill(ds, "Ribs")
            Return ds
            'ds.Tables.Add(GetActionHistory())
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    'Function GetRibsTaskList() As DataTable
    '    Dim dt As New DataTable
    '    Try
    '        Dim Conn As New OleDbConnection(gConnectionString)
    '        Dim Adapter As New OleDbDataAdapter
    '        Dim cmd As New OleDbCommand
    '        Dim SQL As String = "SELECT ActionHistory.*, Ribs.*, LETTER_CODE.Description " & _
    '                                " FROM (LETTER_CODE INNER JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
    '                                " WHERE (((Select Top 1 ActionID  from  ActionHistory Where RibsID = Ribs.RibsID order By EntryDate Desc))=[ActionHistory].[actionid] Or " & _
    '                                " ((Select Top 1 ActionID from ActionHistory Where RibsID = Ribs.RibsID order By EntryDate Desc)) Is Null) " & _
    '                                " " & GetStatusFilter(ReturnType.Tasks) & _
    '                                " ORDER BY ActionHistory.ActionDate ASC "
    '        Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
    '        Adapter.Fill(dt)

    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    Return dt
    'End Function

    Function GetSearchSQL(ByVal Search As String) As String
        Try
            Dim SQL As String = "Where "
            SQL += " RD.POLICY_NUMBER LIKE '%" & Search & "%' "
            SQL += " OR RD.MAILING_NAME LIKE '%" & Search & "%' "
            SQL += " OR RD.ADVISER_SURNAME LIKE '%" & Search & "%' "
            Return SQL
            'Dim SQL As String = "SELECT TOP 1 * FROM Ribs "
            'Dim Conn As New OleDbConnection(gConnectionString)
            'Dim Adapter As New OleDbDataAdapter
            'Dim cmd As New OleDbCommand
            'Dim dt As New DataTable
            'Dim daCol As DataColumn
            'Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            'Adapter.Fill(dt)
            'SQL = " WHERE "
            'For Each daCol In dt.Columns
            '    If daCol.ColumnName <> "RIBSID" And _
            '                        daCol.ColumnName <> "ActionID" And _
            '                        daCol.ColumnName <> "CURRENT_RUN_NUMBER" Then
            '        Select Case daCol.DataType.ToString
            '            Case "System.String"
            '                If SQL <> " WHERE " Then
            '                    SQL += IIf(SQL.ToString = " AND ( ", "", " OR ").ToString
            '                End If
            '                SQL += " Ribs.[" & daCol.ColumnName & "] LIKE '%" & Search & "%' "
            '                'Case "System.Int32"
            '                '    If IsNumeric(Search) Then
            '                '        SQL += IIf(SQL = " WHERE ", "", " OR ")
            '                '        SQL += " Ribs.[" & daCol.ColumnName & "] LIKE " & CInt(Search) & "#####"
            '                '    End If
            '        End Select
            '    End If
            'Next
            'Return SQL + " OR Ribs.[LETTER_CODE] is NULL"
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetStatusFilter(ByVal rtType As ReturnType) As String
        Dim SQL As String = ""
        Try
            Dim str As String = ""
            Dim lStatus() As String '= Split(My.Settings.MyMainStates, ",")

            If rtType = ReturnType.Main Then
                '12/01
                'lStatus = Split(gsMyMainStates, ",")
                If oNetworkUser IsNot Nothing Then lStatus = Split(oNetworkUser.MainFilter, ",")

                SQL = " (( ActionDate <=#" & Format(Now(), "dd MMM yyyy") & "# ) OR (ActionDate IS Null ) )"
            Else
                '12/01
                'lStatus = Split(gsMyTaskStates, ",")
                If oNetworkUser IsNot Nothing Then lStatus = Split(oNetworkUser.TaskFilter, ",")
                SQL = " ( ActionDate >#" & Format(Now(), "dd MMM yyyy") & "# ) "

            End If

            'If lStatus.GetUpperBound(0) >= 0 Then
            '    SQL += " AND (RibsState IS Null OR RibsState= '' "
            '    For Each str In lStatus
            '        If Trim(str) <> "" Then
            '            If (Trim(str) = "Allocated") Or (Trim(str) = "Unallocated") Then
            '                If rtType = ReturnType.Main Then
            '                    SQL += " OR RibsState ='Allocated' "
            '                Else
            '                    SQL += " OR RibsState <> 'Unllocated' "
            '                End If
            '            Else
            '                If (str <> "Unallocated") And (str <> "Allocated") Then
            '                    SQL += " OR RibsState ='" & str & "'"
            '                End If
            '            End If
            '        End If
            '    Next
            '    SQL += "   ) "
            'End If

            'If rtType = ReturnType.Main Then
            'Else
            '    '    SQL += " AND RibsState <> 'Allocated' "
            'End If
            SQL += " AND (USERID = '" & CurrentUser & "')"

        Catch ex As Exception
            Throw ex
        End Try
        Return SQL
    End Function


    'Function GetMyFilter() As String
    '    Dim SQL As String = ""
    '    Try
    '        Dim HideFutureDated As Boolean
    '        Dim lActions() As String
    '        Dim StartingDate As Date
    '        'If SystemFilter Then
    '        '    HideFutureDated = oSysSetting.HideFutureDated
    '        '    lActions = Split(oSysSetting.Actions, ",")
    '        '    StartingDate = CDate(oSysSetting.StartingDate)
    '        'Else
    '        HideFutureDated = oSetting.HideFutureDated
    '        lActions = Split(oSetting.MyActions, ",")
    '        StartingDate = oSetting.MyStartingDate
    '        'End If

    '        If HideFutureDated Then
    '            SQL = " ( ActionHistory.ActionDate <=#" & FormatDateTime(Now(), DateFormat.ShortDate) & "#  "
    '            SQL += " OR ActionHistory.ActionDate is NULL ) AND "
    '        End If
    '        SQL += "  ( LETTER_DATE >=#" & StartingDate & "# ) "

    '        'Dim lState() As String = Split(oSetting.UserStates, ",")
    '        Dim st As String
    '        'SQL += " AND ("
    '        'For Each st In lState
    '        '    If Microsoft.VisualBasic.Right(SQL, 1) <> "(" Then
    '        '        SQL += " OR "
    '        '    End If
    '        '    SQL += " MAILING_STATE ='" & st & "'"
    '        'Next
    '        'SQL += " ) "

    '        If lActions.GetUpperBound(0) >= 0 Then
    '            SQL += " AND ("
    '            For Each st In lActions
    '                If Microsoft.VisualBasic.Right(SQL, 1) <> "(" Then
    '                    SQL += " OR "
    '                End If
    '                If Trim(st) = "" Then
    '                    SQL += " Action IS Null "
    '                    SQL += " OR Action= '' "
    '                Else
    '                    SQL += " Action ='" & st & "'"
    '                End If

    '            Next
    '            SQL += " ) "
    '        End If

    '        SQL += " AND (USERID = '" & CurrentUser & "')"

    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    Return SQL
    'End Function

    'Function GetMyTaskFilter() As String
    '    Dim SQL As String = ""
    '    Try
    '        Dim lActions() As String
    '        If SystemFilter Then
    '            lActions = Split(oSysSetting.TaskActions, ",")
    '        Else
    '            lActions = Split(oSetting.MyTaskActions, ",")
    '        End If

    '        SQL = " AND ( ActionHistory.ActionDate <#" & FormatDateTime(Now(), DateFormat.ShortDate) & "# ) "

    '        'Dim lState() As String = Split(oSetting.UserStates, ",")
    '        Dim st As String
    '        'SQL += " AND ("
    '        'For Each st In lState
    '        '    If Microsoft.VisualBasic.Right(SQL, 1) <> "(" Then
    '        '        SQL += " OR "
    '        '    End If
    '        '    SQL += " MAILING_STATE ='" & st & "'"
    '        'Next
    '        'SQL += " ) "

    '        If lActions.GetUpperBound(0) >= 0 Then
    '            SQL += " AND ("
    '            For Each st In lActions
    '                If Microsoft.VisualBasic.Right(SQL, 1) <> "(" Then
    '                    SQL += " OR "
    '                End If
    '                If Trim(st) = "" Then
    '                    SQL += " Action IS Null "
    '                    '    SQL += " OR Action= '' "
    '                Else
    '                    SQL += " Action ='" & st & "'"
    '                End If

    '            Next
    '            SQL += " ) "
    '        End If

    '        SQL += " AND USERID = '" & CurrentUser & "'"


    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    Return SQL
    'End Function

    'Function GetRibsStats() As DataSet
    '    Try
    '        Dim ds As New DataSet
    '        Dim Conn As New OleDbConnection(gConnectionString)
    '        Dim Adapter As New OleDbDataAdapter
    '        Dim cmd As New OleDbCommand
    '        Adapter.SelectCommand = New OleDbCommand("Select * from Ribs", Conn)
    '        Adapter.Fill(ds, "Ribs")

    '        Adapter.SelectCommand = New OleDbCommand("Select * from ActionHistory ", Conn)
    '        Adapter.Fill(ds, "ActionHistory")
    '        Return ds
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    'End Function

    Function GetRibsItemtab(ByVal ID As Int32) As DataTable
        Try
            Dim dt As New DataTable("Ribs")
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Adapter.SelectCommand = New OleDbCommand("Select * from Ribs Where RibsID =" & ID, Conn)
            Adapter.Fill(dt, "Ribs")
            Return dt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function CheckRibsImported(ByVal Record_ID As String, ByVal POLICY_NUMBER As String, ByVal CURRENT_RUN_NUMBER As Int32) As Boolean
        Try
            Dim SQL As String = "Select ribsID from RIBS WHERE [Record_ID] = '" & Record_ID & "' " & _
                                                " AND POLICY_NUMBER ='" & POLICY_NUMBER & "' " & _
                                                " AND CURRENT_RUN_NUMBER = " & CURRENT_RUN_NUMBER.ToString
            Dim con As New OleDbConnection(gConnectionString)
            Dim cmd As New OleDbCommand(SQL, con)
            Dim Reader As OleDbDataReader

            con.Open()
            Reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
            If Reader.Read() Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetRibsItemByRecordID(ByVal ID As String) As Int32
        Try
            Dim Sql As String = "Select * from RIBS Where RECORD_ID = '" & ID & "'"
            Dim con As New OleDbConnection(gConnectionString)
            Dim cmd As New OleDbCommand(Sql, con)
            Dim Reader As OleDbDataReader

            con.Open()
            Reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
            If Reader.Read() Then
                Return Reader("RibsID")
            Else
                Return 0
            End If

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetRibsItemHistory(ByVal RibsID As Int32, ByVal POLICY_NUMBER As String) As DataSet
        Try
            Dim ds As New DataSet
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String = "SELECT " & _
                               " RIBS.* " & _
                               " FROM Ribs " & _
                               " WHERE POLICY_NUMBER = '" & POLICY_NUMBER & "'" & _
                               " AND RibsID <> " & RibsID & _
                               " AND LETTER_DATE >=  #" & Format(DateAdd(DateInterval.Month, -3, Date.Now), "dd MMM yyyy") & "#"
            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            Adapter.Fill(ds, "Ribs")
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetRibsItem(ByVal ID As Int32, _
                         Optional ByVal Archive As String = "") As DataSet
        Dim ds As New DataSet
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            If Archive.ToUpper.Contains("ARCHIVE") Then
                Archive = "_Archive"
            Else
                Archive = String.Empty
            End If
            'Dim SQL As String = "SELECT ActionHistory.ActionDate, ActionHistory.Action, ActionHistory.RibsState,  " & _
            '                   " RIBS.* " & _
            '                   " FROM Ribs" & Archive & " LEFT JOIN ActionHistory " & Archive & "ON Ribs.RibsID = ActionHistory.RibsID " & _
            '                   " WHERE Ribs.[RibsID]=" & ID & _
            '                   " AND ((Select Top 1 ActionID from ActionHistory " & _
            '                   " Where RibsID = Ribs.RibsID order By  " & _
            '                   " ActionHistory.ActionID Desc)=[ActionHistory].[actionid] " & _
            '                   " OR([ActionHistory].[actionid] is null )) "

            Dim SQL As String = "SELECT ActionHistory.ActionDate, ActionHistory.Action, ActionHistory.RibsState,  " & _
                               " RIBS.* " & _
                               " FROM Ribs" & Archive & " Ribs LEFT JOIN ActionHistory" & Archive & " ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                               " WHERE Ribs.[RibsID]=" & ID & _
                               " AND ((Select Top 1 ActionID from ActionHistory" & Archive & " ActionHistory " & _
                               " Where RibsID = Ribs.RibsID order By  " & _
                               " ActionHistory.ActionID Desc)=[ActionHistory].[actionid] " & _
                               " OR([ActionHistory].[actionid] is null )) "

            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            'Select Top 1 ActionID  from  ActionHistory Where RibsID = Ribs.RibsID order By ActionHistory.EntryDate Desc
            Adapter.Fill(ds, "Ribs")
            ds.Tables.Add(GetActionHistory(ID))
        Catch ex As Exception
            Throw ex
        End Try
        Return ds
    End Function

    Function GetRibsItems(ByVal RibsID As String) As DataTable
        Dim ds As New DataSet
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim dt As New DataTable("Ribs")
            Dim RibsIDs As String = "("
            Dim IDs() As String = Split(RibsID, ",")
            For Each id As String In IDs
                If id <> "" Then
                    RibsIDs += CInt(id) & ","
                End If
            Next

            RibsIDs = Left$(RibsIDs, Len(RibsIDs) - 1) & ")"
            Adapter.SelectCommand = New OleDbCommand("Select * from Ribs Where RibsID IN " & RibsIDs, Conn)
            Adapter.Fill(dt)
            Return dt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetActions(Optional ByVal bClosed As Boolean = False) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim Sql As String = "SELECT * from [Action] WHERE ISVISIBLE = False "
            If bClosed Then
            Else
                Sql += " AND Closed = False"
            End If
            Sql += " ORDER By [Action] "
            Adapter.SelectCommand = New OleDbCommand(Sql, Conn)
            Adapter.Fill(dt)
            dt.TableName = "Action"
        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Function GetStates() As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Adapter.SelectCommand = New OleDbCommand("SELECT * from [RibsState] ORDER By [RibsState] ", Conn)
            Adapter.Fill(dt)
            dt.TableName = "RibsState"
        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Function CheckReason(ByVal Status As String) As Boolean
        Try
            Dim SQL As String = "Select TOP 1 * FROM RibsState WHERE [RibsState] = '" & Status & "' "
            Dim con As New OleDbConnection(gConnectionString)
            Dim cmd As New OleDbCommand(SQL, con)
            Dim Reader As OleDbDataReader

            con.Open()
            Reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
            If Reader.Read() Then
                If Reader("Explain") = True Then
                    Return True
                Else
                    Return False
                End If
            Else
                Return False
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetActionHistory(Optional ByVal ID As Int32 = 0) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim Sql As String = "SELECT * from ActionHistory "
            If ID <> 0 Then
                Sql += "WHERE RibsID = " & ID
            End If
            Sql += " ORDER By ActionID desc "
            Adapter.SelectCommand = New OleDbCommand(Sql, Conn)
            Adapter.Fill(dt)
            dt.TableName = "ActionHistory"
        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    'Function GetRibsStates() As DataTable
    '    Dim dt As New DataTable
    '    Try
    '        Dim Conn As New OleDbConnection(gConnectionString)
    '        Dim Adapter As New OleDbDataAdapter
    '        Dim cmd As New OleDbCommand
    '        Adapter.SelectCommand = New OleDbCommand("SELECT Ribs.MAILING_STATE, false" & _
    '                                                " FROM Ribs GROUP BY Ribs.MAILING_STATE ORDER BY Ribs.MAILING_STATE ", Conn)
    '        Adapter.Fill(dt)
    '        dt.TableName = "States"
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    Return dt
    'End Function

    Function GetRibsLetterCodes() As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Adapter.SelectCommand = New OleDbCommand("SELECT * FRoM LETTER_CODE ORDER By LETTER_CODE ", Conn)
            Adapter.Fill(dt)
            dt.TableName = "LetterCode"
        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    'If ds.Tables("Ribs").Rows.Count > 0 Then
    '   Dim SQL As String = "Select * from Ribs"
    '   Dim Conn As New OleDbConnection(gConnectionString)
    '   Dim Adapter As New OleDbDataAdapter
    '               Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
    '   Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(Adapter)
    '               Conn.Open()
    '               Adapter.Update(ds, "Ribs")
    '               Conn.Close()
    '           End If

    Function UpDateStates(ByVal ldt As DataTable) As Boolean
        Try
            Dim SQL As String
            Dim dr As DataRow
            Dim dt As DataTable

            dt = ldt.GetChanges(DataRowState.Modified)
            If Not IsNothing(dt) Then
                
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "Update RibsState SET "
                        If (dr("RibsState", DataRowVersion.Original) Is dr("RibsState", DataRowVersion.Current)) Or _
                            (dr("Explain", DataRowVersion.Original) Is dr("Explain", DataRowVersion.Current)) Then
                        Else
                            SQL += " RibsState ='" & dr("RibsState") & "', "
                        End If
                        SQL += "  [Closed]=" & IIf(IsDBNull(dr("Closed")), 0, dr("Closed")) & _
                                ",  [Explain]=" & IIf(IsDBNull(dr("Explain")), 0, dr("Explain")) & _
                                " WHERE RibsState ='" & dr("RibsState").ToString & "'"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Added)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "INSERT INTO RibsState (RibsState, [Closed], [Explain]) VALUES (" & _
                                "'" & dr("RibsState") & "'" & _
                                ", " & IIf(IsDBNull(dr("Closed")), 0, dr("Closed")) & _
                                ", " & IIf(IsDBNull(dr("Explain")), 0, dr("Explain")) & ")"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Deleted)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "DELETE * FROM RibsState " & _
                                "WHERE RibsState = '" & dr("RibsState", DataRowVersion.Original) & "'"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function UpDateReason(ByVal ldt As DataTable) As Boolean
        Try
            Dim SQL As String
            Dim dr As DataRow
            Dim dt As DataTable

            dt = ldt.GetChanges(DataRowState.Modified)
            If Not IsNothing(dt) Then

                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "Update [Reason] SET "
                        If (dr("Reason", DataRowVersion.Original) Is dr("Reason", DataRowVersion.Current)) And _
                            dr("Closed", DataRowVersion.Original) Is dr("Closed", DataRowVersion.Current) Then
                        Else
                            SQL += " [Reason] = '" & dr("Reason") & "' "
                            SQL += ", [Closed] = " & dr("Closed")
                            SQL += " WHERE [Reason] = '" & dr("Reason", DataRowVersion.Original).ToString & "'"
                            ExecuteNonQuery(SQL)
                        End If
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Added)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "INSERT INTO [Reason] ([Reason], [Closed]) VALUES (" & _
                                "'" & dr("Reason") & "',0)"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Deleted)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "DELETE * FROM [Reason] " & _
                                "WHERE [Reason]= '" & dr("Reason", DataRowVersion.Original) & "'"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function UpDateContactMethod(ByVal ldt As DataTable) As Boolean
        Try
            Dim SQL As String
            Dim dr As DataRow
            Dim dt As DataTable

            dt = ldt.GetChanges(DataRowState.Modified)
            If Not IsNothing(dt) Then

                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "Update [ContactMethod] SET "
                        If (dr("ContactMethod", DataRowVersion.Original) Is dr("ContactMethod", DataRowVersion.Current)) And _
                            dr("Closed", DataRowVersion.Original) Is dr("Closed", DataRowVersion.Current) Then
                        Else
                            SQL += " [ContactMethod] = '" & dr("ContactMethod") & "' "
                            SQL += ", [Closed] = " & dr("Closed")
                            SQL += " WHERE [ContactMethod] = '" & dr("ContactMethod", DataRowVersion.Original).ToString & "'"
                            ExecuteNonQuery(SQL)
                        End If
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Added)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "INSERT INTO [ContactMethod] ([ContactMethod], [Closed]) VALUES (" & _
                                "'" & dr("ContactMethod") & "'," & dr("Closed") & ")"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Deleted)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "DELETE * FROM [ContactMethod] " & _
                                "WHERE [ContactMethod]= '" & dr("ContactMethod", DataRowVersion.Original) & "'"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function UpDateActions(ByVal ldt As DataTable) As Boolean
        Try
            Dim SQL As String
            Dim dr As DataRow
            Dim dt As DataTable

            dt = ldt.GetChanges(DataRowState.Modified)
            If Not IsNothing(dt) Then

                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "Update [Action] SET "
                        If dr("Action", DataRowVersion.Original) Is dr("Action", DataRowVersion.Current) Then
                        Else
                            SQL += " [Action] = '" & dr("Action") & "', "
                        End If
                        SQL += " [Closed] =" & IIf(IsDBNull(dr("Closed")), 0, CInt(dr("Closed"))) & _
                                " WHERE [Action] = '" & dr("Action", DataRowVersion.Original).ToString & "'"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Added)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "INSERT INTO [Action] ([Action], [Closed]) VALUES (" & _
                                "'" & dr("Action") & "'" & _
                                ", " & IIf(IsDBNull(dr("Closed")), 0, dr("Closed")) & ")"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If

            dt = ldt.GetChanges(DataRowState.Deleted)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        SQL = "DELETE * FROM [Action] " & _
                                "WHERE [Action]= '" & dr("Action", DataRowVersion.Original) & "'"
                        ExecuteNonQuery(SQL)
                    Next
                End If
            End If
            'If dt.Rows.Count > 0 Then
            '    For Each dr As DataRow In dt.Rows
            '        If CheckTableValue("Action", "Action", dr("Action")) Then
            '        Else
            '            ExecuteNonQuery("INSERT INTO [Action] ( [Action], [Closed]) VALUES ('" & dr("Action") & "'," & CInt(dr("Closed")) & " )")
            '        End If
            '    Next
            'End If
            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function DeleteLetter(ByVal ID As Int32) As Boolean
        Try
            Dim SQl As String = "Delete FROM ActionHistory Where RibsID =" & ID
            ExecuteNonQuery(SQl)
            SQl = "Delete FROM Ribs Where RibsID =" & ID
            ExecuteNonQuery(SQl)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function DeleteAction(ByVal ID As Int32) As Boolean
        Try
            Dim SQl As String = "Delete FROM ActionHistory Where ActionID =" & ID
            ExecuteNonQuery(SQl)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function InsertNewAction(ByVal ID As Int32, ByVal Action As String, ByVal Note As String, ByVal ActionDate As Date, ByVal ContactMethod As String, ByVal Status As String, ByVal Reason As String, ByVal lUser As String) As Boolean
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String
            Dim lNote As String = Replace(Note, "'", "''")
            lNote = Replace(lNote, """", "")

            Conn.Open()
            cmd.Connection = Conn
            SQL = "INSERT INTO ActionHistory (RibsID, [Action], [Note], ActionDate, [User], ContactMethod, [RibsState], [Reason] )" & _
                    " VALUES ( " & ID & ",'" & Action & "','" & lNote & "',#" & Format(ActionDate, "dd MMM yyyy") & "#,'" & lUser & "','" & ContactMethod & "','" & Status & "', '" & Reason & "')"
            cmd.CommandText = SQL
            cmd.ExecuteNonQuery()
            Conn.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return True
    End Function

    Public Function UpDateAction(ByVal ActionID As Int32, ByVal Action As String, ByVal Note As String, ByVal ActionDate As Date, ByVal ContactMethod As String, ByVal Status As String, ByVal Reason As String, ByVal lUser As String) As Boolean
        Try
            Dim Sql As String
            Dim lNote As String = Replace(Note, "'", "''")
            lNote = Replace(lNote, """", "")
            Sql = "Update ActionHistory SET " & _
                    " [Action] = '" & Action & "', " & _
                    " [Note] = '" & lNote & "', " & _
                    " ActionDate = " & dbDateFormat(ActionDate) & ", " & _
                    " [User] = '" & lUser & "', " & _
                    " ContactMethod = '" & ContactMethod & "', " & _
                    " [RibsState] = '" & Status & "' " & _
                    " Where ActionID = " & ActionID
            ExecuteNonQuery(SQL)
            Return True
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Function UpdateRibsLetterCodes(ByVal dt As DataTable) As Boolean
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String
            Dim dr As DataRow
            Dim Description As String
            dt = dt.GetChanges(DataRowState.Modified)

            If Not IsNothing(dt) Then
                Conn.Open()
                cmd.Connection = Conn
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        If IsDBNull(dr("Description")) Then
                            Description = "None"
                        Else
                            Description = Replace(dr("Description").ToString, "'", "")
                        End If

                        SQL = "Update LETTER_CODE SET Description ='" & _
                                Description.ToString & "' " & _
                                ", [MinDisplay]=" & dr("MinDisplay").ToString & _
                                " WHERE LETTER_CODE ='" & dr("LETTER_CODE").ToString & "'"
                        cmd.CommandText = SQL
                        cmd.ExecuteNonQuery()
                    Next
                End If
            End If
        Catch ex As Exception
            Throw ex
        End Try
        Return True
    End Function

    Function GetRibsStates(Optional ByVal bClosed As Boolean = False) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String = "Select * From [RibsState] "
            If bClosed Then
                'SQL += " Where (Closed =1 ) AND (Closed =0 )"
            Else
                SQL += " WHERE Closed =0 "
            End If
            SQL += " Order By [RibsState] "
            Adapter.SelectCommand = New OleDbCommand(sql, Conn)
            Adapter.Fill(dt)
        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Function GetReasons(Optional ByVal bClosed As Boolean = False) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String = "Select * From [Reason]  "
            If Not bClosed Then
                SQL += " WHERE Closed = 0"
            End If

            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            Adapter.Fill(dt)

        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Function GetRibsActions(Optional ByVal bDeleted As Boolean = False) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Adapter.SelectCommand = New OleDbCommand("Select * From [Action] Where [System] = False AND Closed =" & CBool(bDeleted) & " AND ISVISIBLE = False Order By [Action] ", Conn)
            Adapter.Fill(dt)

        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Function GetContactMethods(Optional ByVal bClosed As Boolean = False) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String = "Select * From [ContactMethod]  "
            If Not bClosed Then
                SQL += " WHERE Closed = 0"
            End If

            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            Adapter.Fill(dt)

        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Function GetNoticeCode(Optional ByVal bDeleted As Boolean = False) As DataTable
        Dim dt As New DataTable
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Adapter.SelectCommand = New OleDbCommand("Select * From [LETTER_CODE] ", Conn)
            Adapter.Fill(dt)

        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

End Class
